package database;//*******************************************************************
//  DatabaseModfication.java       Java Foundations
//
//  Demonstrates interaction between a Java program and a database.
//*******************************************************************

import java.sql.*;

public class DatabaseModification
{
   //-----------------------------------------------------------------
   //  Carries out various CRUD operations after establishing the
   //  database connection.
   //-----------------------------------------------------------------
  public static void main (String args[])
  {
    Connection conn = null;
    try
    {
      // Loads the class object for the mysql driver into the DriverManager.
      Class.forName("com.mysql.jdbc.Driver");
      
      // Attempt to establish a connection to the specified database via the
      // DriverManager
      conn = DriverManager.getConnection("jdbc:mysql://localhost/" +
        "world?user=root&password=");

      // Check the connection
      if (conn != null)
      {
        System.out.println("We have connected to our database!");
        
        // Create the table and show the table structure
        Statement stmt = conn.createStatement();

      ResultSet result=stmt.executeQuery("SELECT * FROM city WHERE city.Population>6017230");


       DatabaseModification.showResults("city",result);
        
        // Insert the data into the database and show the values in the table
/*        Statement stmt2 = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY,
          ResultSet.CONCUR_UPDATABLE);*/
        Statement stmt1 = conn.createStatement();
        ResultSet result1=stmt1.executeQuery("SELECT * FROM country WHERE country.LifeExpectancy=(SELECT MAX(country.LifeExpectancy)FROM country) ");
        System.out.println("世界上平均寿命最长的国家");
        DatabaseModification.showResults("country",result1);
        Statement stmt2= conn.createStatement();
        ResultSet result2=stmt2.executeQuery("SELECT * FROM country WHERE country.LifeExpectancy=(SELECT MIN(country.LifeExpectancy)FROM country) ");
        System.out.println("世界上平均寿命最短的国家");
        DatabaseModification.showResults("country",result2);
        // Close the database
        Statement stmt3=conn.createStatement();
        ResultSet result3=stmt3.executeQuery("SELECT SUM(Population) FROM country WHERE country.Region=\"Middle East\"");
        System.out.println("世界上所有中东国家的总人口");
        DatabaseModification.showResults("country",result3);
        conn.close();
      }
      
    } catch (SQLException ex) {
      System.out.println("SQLException: " + ex.getMessage());
      ex.printStackTrace();
      
    } catch (Exception ex) {
      System.out.println("Exception: " + ex.getMessage());
      ex.printStackTrace();
    }
  }
  
   //-----------------------------------------------------------------
   //  Obtains and displays a ResultSet from the Student table.
   //-----------------------------------------------------------------
  public static void showValues(Connection conn)
  {
    try
    {
      Statement stmt = conn.createStatement();
      ResultSet rset = stmt.executeQuery("SELECT * FROM Student");
      DatabaseModification.showResults("Student", rset);
    } catch (SQLException ex) {
      System.out.println("SQLException: " + ex.getMessage());
      ex.printStackTrace();
    }
  }
  
   //-----------------------------------------------------------------
   //  Displays the structure of the Student table.
   //-----------------------------------------------------------------
  public static void showColumns(Connection conn)
  {
    try
    {
      Statement stmt = conn.createStatement();
      ResultSet rset = stmt.executeQuery("SHOW COLUMNS FROM Student");
      DatabaseModification.showResults("Student", rset);
    } catch (SQLException ex) {
      System.out.println("SQLException: " + ex.getMessage());
      ex.printStackTrace();
    }
  }
  
   //-----------------------------------------------------------------
   //  Displays the contents of the specified ResultSet.
   //-----------------------------------------------------------------
  public static void showResults(String tableName, ResultSet rSet)
  {
    try
    {
      ResultSetMetaData rsmd = rSet.getMetaData();
      int numColumns = rsmd.getColumnCount();
      String resultString = null;
      if (numColumns > 0)
      {
        resultString = "\nTable: " + tableName + "\n" +
          "=======================================================\n";
        for (int colNum = 1; colNum <= numColumns; colNum++) {
            resultString += rsmd.getColumnLabel(colNum) + "     ";
        }
      }
      System.out.println(resultString);
      System.out.println(
        "=======================================================");
  
      while (rSet.next())
      {
        resultString = "";
        for (int colNum = 1; colNum <= numColumns; colNum++)
        {
          String column = rSet.getString(colNum);
          if (column != null) {
              resultString += column + "     ";
          }
        }
        System.out.println(resultString + '\n' +
          "-------------------------------------------------------");
      }
    } catch (SQLException ex) {
      System.out.println("SQLException: " + ex.getMessage());
      ex.printStackTrace();
    }
  }
}